If you need to quickly create a JPA-based repository layer, Spring Data JPA is the right choice. You define your repository interface by extending one of the Spring Data JPA Repository interfaces.  At runtime, Spring Data JPA will create your repository implementations with the common CRUD methods. You can then perform CRUD operations without writing a single line of data access code.

But by saying so, Enterprise Applications developed using the Spring Framework often needs to execute complex queries against the database.

In such a scenario, you need to inform Spring Data JPA on what queries you need to execute. You do it using the @Query annotation.

In the post, I will show you how to use @Query annotation to execute custom queries.

I will use a Spring Boot application with a Book entity.  I will also use Lombok to generate code for the Book entity.

To store entities, I will go with an embedded H2 database.

The Spring Data JPA, Lombok, and H2 dependencies in the pom.xml file is this.

Next, I will add configuration properties in the application.properties file to connect with the embedded H2 database.

The application.properties file is this.

The code of the Book entity is this.

Book.java

In the preceding code, @Entity(name = "Book") annotation specifies that the class is an entity mapped to a table named Book. The other class-level annotations are Lombok annotations to reduce boilerplate code in this entity class.

The repository interface extends CrudRepository. Here, I will use the @Query annotation to create a custom query to find all books.

The code of the BookRepository is this.

In the preceding code, the findAllBooks() method is annotated with the @Query annotation. This annotation takes a custom query as a string. In this example, the custom query returns all books.

To test this method, I will write a JUnit 5 test.

The BookRepositoryTest class is this.

BookRepositoryTest.java

This is an integration test. However, instead of loading the entire application context using @SpringBootTest, I used the @DataJpa annotation. This will load the JPA slice of the application context making the test lightweight and fast.

The setUp() method annotated with @BeforeEach saves Book objects to the database.

The findAllBooks() test method calls the bookRepository.findAllBooks() method.

Finally, the code performs assertions on the returned result using the AssertJ library.

One way to pass method parameters to a query is through an index.

Let’s define a custom query using Java Persistence Query Language (JPQL) to find a book for a given title and author.

The code for querying a book with index parameters using JPQL is this.

In the preceding code, the title method parameter will be assigned to the query parameter with index 1. Similarly, authorName will be assigned to the query parameter with index 2.

It is important to note that the order of the query parameter indexes and the method parameters must be the same.

Therefore, this code will not work.

The code to test the query is this.

For a custom Native SQL query, you need to set the nativequery flag to true.

The code to use Native SQL is this.

In the preceding code, I’m setting the nativeQuery flag to true. At runtime, the title and authorName method parameters will be assigned to the query parameter with index 1 and 2 respectively.

The test code is this.

Another approach to pass parameters to a query is with named parameters. In this approach, you pass the method parameter values to the query bind parameters.

In order to do so, use the @Param annotation with the name of the bind parameter in the method definition.

The code for querying a book with named parameters is this.

The  @Param annotation in the preceding code binds the names of the query parameters with the method parameters.

The test code is this.

To query with named parameters using Native SQL is similar to JPQL.

The code to query is this.

The test code is this.

To sort query results, in Spring Data JPA provides a special Sort parameter. When you use the Sort parameter, Spring Data JPA will generate the ORDER_BY clause automatically.

The code to perform sorting is this.

In the preceding code, the Sort parameter orders the books retrieved by title according to the sorting properties set on it by the client code.

The test code is this.

Note: Spring Data JPA does not currently support dynamic sorting for native queries

Spring Data JPA also supports Spring Expression Language SpEL expressions. An SpEL expression is evaluated against a predefined set of variables. You can replace the actual entity name with entityName expression variable.

The code to demonstrate SpEL expression is this.

In the preceding code, Spring Data JPA inserts the entityName of the domain type associated with the given repository, Book in our case.

The test code is this.

Spring Data JPA provides a specialPageableparameter to paginate query results.

With Pagination, you get a Page object as a result. A Page object is the subset of the complete result.

The code to perform pagination is this.

The test code is this.

Summary

When you are into Enterprise Application Development with the Spring Framework, you will have to deal with complex queries.

For such queries, you cannot always look for the comfort of Derived queries. For example, if you use more than 2-3 query parameters or need to define multiple joins to other entities, you need a more flexible approach.

In such situations, you can use Spring Data JPA’s @Query annotation to specify a custom JPQL or native SQL query.

With @Query Spring Data JPA provides the required JPA code to execute the statement as a JPQL or native SQL query. Your preferred JPA implementation,  such as, Hibernate or EclipseLink, will then execute the query and map the result.

Another advantage of using the Spring Data JPA @Query annotation is related to code manageability.

With @Query, you are binding the queries to the Java method that executes them. By using this approach over annotating the domain class, you free the domain class from storing any persistence specific information. Also, it is convenient to co-locate the queries in the repository interface.

The source code for this post can be found here on GitHub.